package dao.impl;

import core.BaseDao;
import dao.TableDao;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import pojo.Table;
import pojo.TableInfo;
import util.MyMapListHandler;

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class TableDaoImpl extends BaseDao implements TableDao {

    protected Connection connection;

    public TableDaoImpl() {
        
    }

    public void setConnection(Connection connection){
        this.connection=connection;
    }
    public Connection getConnection() {
        return connection;
    }

    /**
     * 根据餐桌类型查找餐桌编号
     * @param tableType
     * @return
     */
    @Override
    public Integer findTableCodeByType(String tableType) {
        try {
            sql = "SELECT table_code FROM `table` WHERE table_type=? ORDER BY table_id DESC";
            Object query = queryRunner.query(sql, new ScalarHandler<>(), tableType);
            if (query == null){
                query=1;
            }else {
                return Integer.valueOf(query.toString())+1;
            }
            return Integer.valueOf(query.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 编辑
     * @param table
     * @return
     */
    @Override
    public Integer edit(Table table) {
        try {
            sql = "UPDATE `table` SET table_name=?,table_type=?,table_code=?,table_capacity=?,table_update_time=? WHERE table_id = ?";
            return queryRunner.update(sql,
                    table.getTableName(),
                    table.getTableType(),
                    table.getTableCode(),
                    table.getTableCapacity(),
                    table.getTableUpdateTime(),
                    table.getTableId()
            );
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 通过ID查找餐桌信息
     * @param tableId
     * @return
     */
    @Override
    public Table findEditTable(String tableId) {
        try {
            sql = "SELECT * FROM `table` WHERE table_id = ?";
            return queryRunner.query(sql, new BeanHandler<>(Table.class, rowProcessor), tableId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new Table();
    }

    /**
     * 检查编辑信息是否存在
     * @param table
     * @return
     */
    @Override
    public Integer checkEditInfo(Table table) {
        try {
            sql = "SELECT COUNT(1) FROM `table` WHERE table_name = ? AND table_id != ?";
            if (Integer.valueOf(queryRunner.query(sql, new ScalarHandler<>(), table.getTableName(), table.getTableId()).toString())>0) {
                return 1;
            }
            sql = "SELECT COUNT(1) FROM `table` WHERE table_code = ? AND table_id != ? AND table_type != ?";
            if (Integer.valueOf(queryRunner.query(sql, new ScalarHandler<>(), table.getTableCode(), table.getTableId(), table.getTableType()).toString())>0) {
                return 2;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 检查登录信息是否存在
     * @param table
     * @return
     */
    @Override
    public Integer checkAddInfo(Table table) {
        try {
            sql = "SELECT COUNT(1) FROM `table` WHERE table_name = ? AND table_type = ?";
            if (Integer.valueOf(queryRunner.query(sql, new ScalarHandler<>(), table.getTableName(), table.getTableType()).toString())>0) {
                return 1;
            }
            sql = "SELECT COUNT(1) FROM `table` WHERE table_code = ? AND table_type = ?";
            if (Integer.valueOf(queryRunner.query(sql, new ScalarHandler<>(), table.getTableCode(), table.getTableType()).toString())>0) {
                return 2;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 查找最新添加的餐桌
     * @param table
     * @return
     */
    @Override
    public Table findNewTable(Table table) {
        try {
            sql = "SELECT * FROM `table` WHERE table_name=? AND table_type=? LIMIT 1";
            return queryRunner.query(sql, new BeanHandler<>(Table.class, rowProcessor), table.getTableName(), table.getTableType());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new Table();
    }

    /**
     * 添加餐桌信息
     * @param tableId
     * @return
     */
    @Override
    public Integer addTableInfo(Integer tableId) {
        try {
            sql = "INSERT INTO table_info(table_id) VALUES(?)";
            return queryRunner.update(sql, tableId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 取消餐桌预定
     * @param tableId
     * @return
     */
    @Override
    public Integer cancelBookTable(String tableId) {
        try {
            sql = "UPDATE `table` SET table_status = 0 WHERE table_id=?";
            return queryRunner.update(sql, tableId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 预约
     * @param tableId
     * @return
     */
    @Override
    public Integer bookTable(String tableId) {
        try {
            sql = "UPDATE `table` SET table_status = 2 WHERE table_id = ?";
            return queryRunner.update(sql, tableId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 开台-设置餐桌状态为正在使用
     * @param tableId
     * @return
     */
    @Override
    public Integer setUpTable(String tableId) {
        try {
            sql = "UPDATE `table` SET table_status=1 WHERE table_id=?";
            return queryRunner.update(sql, tableId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 查询所有table
     * @return
     */
    @Override
    public List<Table> tableList() {
        try {
            sql = "SELECT * FROM `table` t, table_info ti WHERE t.table_id=ti.table_id GROUP BY t.table_id";
            List<Map<String, Object>> query = queryRunner.query(sql, new MyMapListHandler());
            List<Table> tableList = new ArrayList<>();
            for (Map<String, Object> objectMap : query) {
                Table table = new Table();
                TableInfo tableInfo = new TableInfo();
                BeanUtils.populate(table, objectMap);
                BeanUtils.populate(tableInfo, objectMap);
                table.setTableInfo(tableInfo);
                tableList.add(table);
            }
            List<Table> tableList1 = new ArrayList<>();
            sql = "SELECT * FROM `table` t, table_info ti WHERE t.table_id=ti.table_id AND t.table_id=? ORDER BY ti.table_info_id DESC LIMIT 1";
            for (Table table : tableList) {
                List<Map<String, Object>> maps = queryRunner.query(sql, new MyMapListHandler(), table.getTableId());
                Table table1 = new Table();
                TableInfo tableInfo1 = new TableInfo();
                BeanUtils.populate(table1, maps.get(0));
                BeanUtils.populate(tableInfo1, maps.get(0));
                table.setTableInfo(tableInfo1);
                tableList1.add(table);
            }
            return tableList1;
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        return new ArrayList<>();
    }

    /**
     * 恢复
     * @param tableId
     * @return
     */
    @Override
    public Integer restore(String tableId) {
        try {
            String sql = "UPDATE `table` SET table_is_deleted=0 AND table_update_time=? WHERE table_id=?";
            return queryRunner.update(sql, new Timestamp(System.currentTimeMillis()), tableId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 真删除
     * @param tableId
     * @return
     */
    @Override
    public Integer realDelete(String tableId) {
        try {
            sql = "DELETE FROM `table_info` WHERE table_id=?";
            queryRunner.update(sql, tableId);
            sql = "DELETE FROM `table` WHERE table_id=?";
            return queryRunner.update(sql, tableId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 软删除
     * @param tableId
     * @return
     */
    @Override
    public Integer delete(Table table, String tableId) {
        try {
            // 仅能删除空闲状态的餐桌
            sql = "SELECT * FROM `table` WHERE table_id=? AND table_status!=0";
            Object query = queryRunner.query(sql, new ScalarHandler<>(), tableId);
            if (query != null) {
                return 2;
            }
            // 执行软删除
            sql = "UPDATE `table` SET table_is_deleted=1,table_update_time=? WHERE table_id=?";
            return queryRunner.update(sql, new Timestamp(System.currentTimeMillis()), tableId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }


    /**
     * 根据条件查找tableList的个数
     * @param table
     * @return
     */
    @Override
    public Integer countTableCodeByParams(Table table) {
        try {
            sql =  "SELECT count(1) FROM `table` WHERE table_is_deleted=?";
            List<String> params = new ArrayList<>();
            if (table.getTableName() != null && !table.getTableName().isEmpty()){
                sql += " AND table_name LIKE ?";
                params.add("%"+table.getTableName()+"%");
            }
            if (table.getTableType() != null && !table.getTableType().isEmpty()){
                sql += " AND table_type = ?";
                params.add(table.getTableType());
            }
            if (table.getTableStatus() != null && !"-1".equals(table.getTableStatus().toString())){
                sql += " AND table_status = ?";
                params.add(table.getTableStatus().toString());
            }

            if (params.size() == 3){
                return Integer.valueOf(queryRunner.query(sql, new ScalarHandler<>(), table.getTableIsDeleted(), params.get(0), params.get(1), params.get(2)).toString());
            }
            else if (params.size() == 2){
                return Integer.valueOf(queryRunner.query(sql, new ScalarHandler<>(), table.getTableIsDeleted(), params.get(0), params.get(1)).toString());
            }else if (params.size() == 1){
                return Integer.valueOf(queryRunner.query(sql, new ScalarHandler<>(), table.getTableIsDeleted(), params.get(0)).toString());
            }else if (params.size() == 0){
                return Integer.valueOf(queryRunner.query(sql, new ScalarHandler<>(), table.getTableIsDeleted()).toString());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 根据条件查找tableList
     * @param table
     * @return
     */
    @Override
    public List<Table> findTableCodeByParams(Table table) {
        try {
            List<Table> tableList = new ArrayList<>();
            sql =  "SELECT * FROM `table` WHERE table_is_deleted=?";
            List<String> params = new ArrayList<>();
            if (table.getTableName() != null && !table.getTableName().isEmpty()){
                sql += " AND table_name LIKE ?";
                params.add("%"+table.getTableName()+"%");
            }
            if (table.getTableType() != null && !table.getTableType().isEmpty()){
                sql += " AND table_type = ?";
                params.add(table.getTableType());
            }
            if (table.getTableStatus() != null && !"-1".equals(table.getTableStatus().toString())){
                sql += " AND table_status = ?";
                params.add(table.getTableStatus().toString());
            }
            if (table.getPage() == null && table.getLimit() == null){
                List<Map<String, Object>> query = queryRunner.query(sql, new MyMapListHandler(), table.getTableIsDeleted());
                for (Map<String, Object> map : query) {
                    Table table1 = new Table();
                    BeanUtils.populate(table1, map);
                    tableList.add(table1);
                }
                return tableList;
            }

            sql += " ORDER BY table_id DESC  LIMIT ?,?";

            Integer page = (table.getPage()-1)*table.getLimit();
            Integer limit = table.getLimit();

            if (params.size() == 3){
                List<Map<String, Object>> query = queryRunner.query(sql, new MyMapListHandler(), table.getTableIsDeleted(), params.get(0), params.get(1), params.get(2), page, limit);
                for (Map<String, Object> map : query) {
                    Table table1 = new Table();
                    BeanUtils.populate(table1, map);
                    tableList.add(table1);
                }
                return tableList;
            }
            else if (params.size() == 2){
                List<Map<String, Object>> query = queryRunner.query(sql, new MyMapListHandler(), table.getTableIsDeleted(), params.get(0), params.get(1), page, limit);
                for (Map<String, Object> map : query) {
                    Table table1 = new Table();
                    BeanUtils.populate(table1, map);
                    tableList.add(table1);
                }
                return tableList;
            }else if (params.size() == 1){
                List<Map<String, Object>> query = queryRunner.query(sql, new MyMapListHandler(), table.getTableIsDeleted(), params.get(0), page, limit);
                for (Map<String, Object> map : query) {
                    Table table1 = new Table();
                    BeanUtils.populate(table1, map);
                    tableList.add(table1);
                }
                return tableList;
            }else if (params.size() == 0){
                List<Map<String, Object>> query = queryRunner.query(sql, new MyMapListHandler(), table.getTableIsDeleted(), page, limit);
                for (Map<String, Object> map : query) {
                    Table table1 = new Table();
                    BeanUtils.populate(table1, map);
                    tableList.add(table1);
                }
                return tableList;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        return new ArrayList<>();
    }

    /**
     * 添加餐桌
     * @param table
     * @return
     */
    @Override
    public Integer add(Table table) {
        try {
            /*sql = "INSERT INTO table_info(table_id) VALUES(?)";
            queryRunner.update(sql, table.getTableId());*/
            sql = "INSERT INTO `table`(table_name,table_code,table_type,table_capacity,table_create_time,table_update_time) VALUES (?,?,?,?,?,?)";
            return queryRunner.update(sql,
                    table.getTableName(),
                    table.getTableCode(),
                    table.getTableType(),
                    table.getTableCapacity(),
                    table.getTableCreateTime(),
                    table.getTableUpdateTime()
            );
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 取消餐桌预定
     * @param tableId
     * @return
     */
    public Integer cancelBookTable(String tableId, Connection connection) {
        try {
            sql = "UPDATE `table` SET table_status = 0 WHERE table_id=?";
            return queryRunner.update(connection, sql, tableId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}
